from PySide6.QtWidgets import (
    QGridLayout, QProgressBar, QSpacerItem, QHeaderView,
    QFrame, QMessageBox, QVBoxLayout, QHBoxLayout, QTableWidgetItem, QSizePolicy, QWidget, QCompleter, QApplication,
    QLabel
)
from PySide6.QtCore import Qt, QSize
from PySide6.QtGui import QFont
from qfluentwidgets import TableWidget, ZhDatePicker, ComboBox, SearchLineEdit, BodyLabel, PushButton, FluentIcon, \
    DatePicker
from util import dbUtil
# from DAO.DataImport import dataImport
from models.TopBar import TopBar

import os
from datetime import datetime
import pandas as pd
from PySide6.QtCore import QStandardPaths
from PySide6.QtWidgets import QWidget, QVBoxLayout, QPushButton, QFileDialog, QMessageBox
from util import dbUtil


class ItemSelectBar(QWidget):
    def __init__(self, itemDataArea):
        super().__init__()
        self.itemSelectComboBox = None
        self.searchItemLineEdit = None
        self.endDateEdit = None
        self.startDateEdit = None
        self.startDateLabel = None
        self.endDateLabel = None
        self.gridLayout = None
        self.itemDataArea = itemDataArea  # 传入 ItemDataArea 实例
        self.setupUi()

    def setupUi(self):
        self.setFixedHeight(80)
        self.gridLayout = QGridLayout(self)
        self.gridLayout.setHorizontalSpacing(30)
        self.gridLayout.setVerticalSpacing(15)
        self.gridLayout.setContentsMargins(20, 0, 20, 0)

        font = QFont()
        font.setPointSize(10)

        self.startDateLabel = BodyLabel("开始日期")
        self.startDateLabel.setFont(font)
        self.startDateLabel.setAlignment(Qt.AlignmentFlag.AlignCenter)
        self.gridLayout.addWidget(self.startDateLabel, 0, 1, 1, 1)

        self.endDateLabel = BodyLabel("结束日期")
        self.endDateLabel.setFont(font)
        self.endDateLabel.setAlignment(Qt.AlignmentFlag.AlignCenter)
        self.gridLayout.addWidget(self.endDateLabel, 0, 2, 1, 1)

        self.startDateEdit = ZhDatePicker()
        self.startDateEdit.setMinimumSize(QSize(140, 35))
        self.gridLayout.addWidget(self.startDateEdit, 1, 1, 1, 1)

        self.endDateEdit = ZhDatePicker(self)
        self.endDateEdit.setMinimumSize(QSize(0, 35))
        self.gridLayout.addWidget(self.endDateEdit, 1, 2, 1, 1)

        self.itemSelectComboBox = ComboBox(self)
        self.itemSelectComboBox.setPlaceholderText("请选择项目")
        self.itemSelectComboBox.setCurrentIndex(-1)
        sizePolicy = QSizePolicy(QSizePolicy.Policy.Fixed, QSizePolicy.Policy.Fixed)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.itemSelectComboBox.sizePolicy().hasHeightForWidth())
        self.itemSelectComboBox.setSizePolicy(sizePolicy)
        self.itemSelectComboBox.setMinimumSize(QSize(220, 35))
        self.gridLayout.addWidget(self.itemSelectComboBox, 1, 0, 1, 1)

        self.searchItemLineEdit = SearchLineEdit()
        self.searchItemLineEdit.setPlaceholderText("请输入项目名称")
        sizePolicy.setHeightForWidth(self.searchItemLineEdit.sizePolicy().hasHeightForWidth())
        self.searchItemLineEdit.setSizePolicy(sizePolicy)
        self.searchItemLineEdit.setMinimumSize(QSize(220, 33))
        self.gridLayout.addWidget(self.searchItemLineEdit, 0, 0, 1, 1)

        self.gridLayout.setAlignment(self.itemSelectComboBox, Qt.AlignmentFlag.AlignLeft)
        self.gridLayout.setAlignment(self.searchItemLineEdit, Qt.AlignmentFlag.AlignLeft)

        self.searchItemLineEdit.textChanged.connect(self.filterItems)
        self.itemSelectComboBox.currentIndexChanged.connect(self.updateTable)
        self.startDateEdit.dateChanged.connect(self.updateTable)
        self.endDateEdit.dateChanged.connect(self.updateTable)

        self.populateComboBoxAndSearch()
        self.itemDataArea.populateTable(project_name=None, start_date=None, end_date=None) # 初始化时填充表格

    def populateComboBoxAndSearch(self):
        conn = dbUtil.get_db_conn()
        if not conn:
            QMessageBox.critical(self, "连接失败", "无法连接到数据库。")
            return

        try:
            with conn.cursor() as cursor:
                query = "SELECT 项目名称 FROM cq_item"
                cursor.execute(query)
                results = cursor.fetchall()

                project_names = [row[0] for row in results]

                # 添加默认选项
                self.itemSelectComboBox.addItem("全部")
                self.itemSelectComboBox.addItems(project_names)
                self.itemSelectComboBox.setCurrentIndex(0)
                self.searchItemLineEdit.setCompleter(QCompleter(project_names))

        except Exception as e:
            error_message = str(e)
            print(error_message)
            QMessageBox.critical(self, "数据加载失败", f"发生错误: {error_message}")
        finally:
            if conn:
                dbUtil.close_db_conn(conn)

    def filterItems(self, text):
        for i in range(self.itemSelectComboBox.count()):
            if text.lower() in self.itemSelectComboBox.itemText(i).lower():
                self.itemSelectComboBox.setCurrentIndex(i)
                break

    def updateTable(self):
        project_name = self.itemSelectComboBox.currentText()
        # 如果选择了 "全部" 选项，则将 project_name 设置为 None
        if project_name == "全部":
            project_name = None
        start_date = self.startDateEdit.date.toString("yyyy-MM-dd")
        end_date = self.endDateEdit.date.toString("yyyy-MM-dd")
        self.itemDataArea.populateTable(project_name, start_date, end_date)


class ItemDataArea(QWidget):
    def __init__(self):
        super().__init__()
        self.totalPages = 0
        self.totalItems = 0
        self.pPageLabel = None
        self.pNextButton = None
        self.pPrevButton = None
        self.paginatorLayout = None
        self.itemTable = None
        self.mainLayout = None
        self.currentPage = 1
        self.itemsPerPage = 8
        self.setupUi()

    def setupUi(self):
        self.setContentsMargins(20, 0, 20, 0)

        self.mainLayout = QVBoxLayout()
        self.setLayout(self.mainLayout)
        self.mainLayout.setSpacing(15)
        self.mainLayout.setContentsMargins(0, 0, 0, 0)

        self.itemTable = TableWidget()
        self.itemTable.setColumnCount(4)
        self.itemTable.setRowCount(self.itemsPerPage)

        for row in range(self.itemTable.rowCount()):
            self.itemTable.setRowHeight(row, 36)
        tableSizePolicy = QSizePolicy(QSizePolicy.Policy.Expanding, QSizePolicy.Policy.Fixed)
        self.itemTable.setSizePolicy(tableSizePolicy)
        self.itemTable.setHorizontalHeaderLabels(["序号", "名称", "导入用户", "导入时间"])
        header = self.itemTable.horizontalHeader()
        header.setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
        self.itemTable.verticalHeader().setVisible(False)
        self.mainLayout.addWidget(self.itemTable)

        self.paginatorLayout = QHBoxLayout()
        self.paginatorLayout.setSpacing(10)
        self.paginatorLayout.setContentsMargins(120, 0, 120, 0)
        self.pPrevButton = PushButton("上一页", self)
        self.pPrevButton.setIcon(FluentIcon.CARE_LEFT_SOLID)
        self.pPrevButton.setObjectName("pPrevButton")
        self.pPrevButton.setFixedSize(150, 30)
        self.pNextButton = PushButton("下一页", self)
        self.pNextButton.setIcon(FluentIcon.CARE_RIGHT_SOLID)
        self.pNextButton.setObjectName("pNextButton")
        self.pNextButton.setFixedSize(150, 30)
        self.pPageLabel = BodyLabel()
        self.paginatorLayout.addWidget(self.pPrevButton)
        self.paginatorLayout.addWidget(self.pPageLabel)
        self.paginatorLayout.addWidget(self.pNextButton)
        self.mainLayout.addLayout(self.paginatorLayout)

        self.pPrevButton.clicked.connect(self.prevPage)
        self.pNextButton.clicked.connect(self.nextPage)

        self.populateTable()

    def populateTable(self, project_name=None, start_date=None, end_date=None):
        # 清空表格内容
        self.itemTable.clearContents()

        # 从数据库获取数据
        conn = dbUtil.get_db_conn()
        if not conn:
            QMessageBox.critical(self, "连接失败", "无法连接到数据库。")
            return

        try:
            with conn.cursor() as cursor:
                # 构建 WHERE 条件
                conditions = []
                if project_name:
                    conditions.append(f"项目名称 LIKE '%{project_name}%'")
                if start_date:
                    conditions.append(f"导入时间 >= '{start_date}'")
                if end_date:
                    conditions.append(f"导入时间 <= '{end_date}'")

                where_clause = " AND ".join(conditions)
                if where_clause:
                    where_clause = " WHERE " + where_clause

                # 获取总项目数
                count_query = f"SELECT COUNT(*) FROM cq_item {where_clause}"
                cursor.execute(count_query)
                self.totalItems = cursor.fetchone()[0]
                self.totalPages = (self.totalItems + self.itemsPerPage - 1) // self.itemsPerPage

                # 获取当前页的数据
                startRow = (self.currentPage - 1) * self.itemsPerPage
                query = f"""
                    SELECT 项目ID, 项目名称, 导入用户, 导入时间
                    FROM cq_item
                    {where_clause}
                    ORDER BY 导入时间 DESC
                    LIMIT {startRow}, {self.itemsPerPage}
                """
                cursor.execute(query)
                results = cursor.fetchall()

                for tableRow, row in enumerate(results):
                    project_id, project_name, import_user, import_time = row
                    self.itemTable.setItem(tableRow, 0, QTableWidgetItem(f"{project_id}"))
                    self.itemTable.setItem(tableRow, 1, QTableWidgetItem(f"{project_name}"))
                    self.itemTable.setItem(tableRow, 2, QTableWidgetItem(f"{import_user}"))
                    self.itemTable.setItem(tableRow, 3, QTableWidgetItem(f"{import_time}"))

                    # 设置单元格文本居中
                    for col in range(4):
                        item = self.itemTable.item(tableRow, col)
                        if item is not None:
                            item.setTextAlignment(Qt.AlignmentFlag.AlignCenter)

        except Exception as e:
            error_message = str(e)
            print(error_message)
            QMessageBox.critical(None, "数据加载失败", f"发生错误: {error_message}")
        finally:
            if conn:
                dbUtil.close_db_conn(conn)

        # 自动调整表格高度以适应行数
        self.itemTable.setFixedHeight(
            self.itemTable.verticalHeader().sectionSize(
                0) * self.itemsPerPage + self.itemTable.horizontalHeader().height() + 5
        )
        self.updatePageLabel()
        self.updateButtons()

    def prevPage(self):
        if self.currentPage > 1:
            self.currentPage -= 1
            self.populateTable()

    def nextPage(self):
        if self.currentPage < self.totalPages:
            self.currentPage += 1
            self.populateTable()

    def updatePageLabel(self):
        self.pPageLabel.setText(f"第 {self.currentPage} 页，共 {self.totalPages} 页")
        self.pPageLabel.setAlignment(Qt.AlignmentFlag.AlignCenter)

    def updateButtons(self):
        self.pPrevButton.setEnabled(self.currentPage > 1)
        self.pNextButton.setEnabled(self.currentPage < self.totalPages)


class ButtonBar(QWidget):
    def __init__(self, itemDataArea):
        super().__init__()
        self.itemDataArea = itemDataArea
        self.setupUi()

    def setupUi(self):
        self.layout = QGridLayout(self)
        self.layout.setContentsMargins(-1, 15, -1, 15)

        self.pushButton = PushButton("导入数据", self)
        self.pushButton.setIcon(FluentIcon.FOLDER)
        self.pushButton.setMinimumSize(QSize(150, 40))
        self.layout.addWidget(self.pushButton, 0, 0, 1, 1)
        self.pushButton.clicked.connect(self.dataImport)

        self.pushButton_2 = PushButton("导出数据", self)
        self.pushButton_2.setIcon(FluentIcon.SEND)
        self.pushButton_2.setMinimumSize(QSize(150, 40))
        self.layout.addWidget(self.pushButton_2, 1, 0, 1, 1)

        self.pushButton_3 = PushButton("编辑数据", self)
        self.pushButton_3.setIcon(FluentIcon.EDIT)
        self.pushButton_3.setMinimumSize(QSize(150, 40))
        self.layout.addWidget(self.pushButton_3, 0, 2, 1, 1)

        self.pushButton_4 = PushButton("删除数据", self)
        self.pushButton_4.setIcon(FluentIcon.DELETE)
        self.pushButton_4.setMinimumSize(QSize(150, 40))
        self.layout.addWidget(self.pushButton_4, 1, 2, 1, 1)

        self.layout.addItem(QSpacerItem(40, 20, QSizePolicy.Expanding, QSizePolicy.Minimum), 0, 3, 1, 1)

        self.progressBar = QProgressBar(self)
        self.progressBar.setMinimumSize(QSize(400, 60))
        self.progressBar.setValue(24)
        self.layout.addWidget(self.progressBar, 0, 4, 2, 1)

    def dataImport(self):
        # 默认打开文档目录
        documents_path = QStandardPaths.writableLocation(QStandardPaths.StandardLocation.DocumentsLocation)

        # 打开文件选择对话框
        file_path, _ = QFileDialog.getOpenFileName(None, '选择导入的数据', documents_path, 'Excel文件 (*.xlsx *.xls)')

        if file_path:
            # 获取文件名（不包括扩展名）
            file_name = os.path.basename(file_path)
            file_name, _ = os.path.splitext(file_name)

            # 连接到数据库
            conn = dbUtil.get_db_conn()
            if not conn:
                QMessageBox.critical(self, "连接失败", "无法连接到数据库。")
                return

            try:
                with conn.cursor() as cursor:
                    """
                    2.1 一个xlsx文件可能会有多个sheet，每个sheet的为一个矿井，获取sheet名称作为矿井名，加入到数据库cq_well中
                    2.2 每一个sheet做单独处理，新建$well_itemName_fileName$表，用来存储此矿井中的数据
                    """

                    xls = pd.ExcelFile(file_path)
                    sheet_names = xls.sheet_names

                    # 获取当前的日期和时间
                    current_datetime = datetime.now()
                    current_datetime_truncated = current_datetime.replace(microsecond=0)

                    # 暂时指定用户为admin，后期改为登录用户
                    user = "admin"

                    # 插入到 cq_item 表
                    insert_cq_item = "INSERT INTO cq_item (项目名称, 导入用户, 导入时间) VALUES (%s, %s, %s)"
                    cursor.execute(insert_cq_item, (file_name, user, current_datetime_truncated))
                    item_id = cursor.lastrowid  # 获取插入的项目ID

                    for well_name in sheet_names:
                        # 读取当前工作表
                        df = pd.read_excel(xls, sheet_name=well_name, skiprows=2)  # 跳过第一行标题和第二行单位

                        # 插入到 cq_well 表
                        insert_cq_well = "INSERT INTO cq_well (矿井名称, 项目ID) VALUES (%s, %s)"
                        cursor.execute(insert_cq_well, (well_name, item_id))
                        well_id = cursor.lastrowid  # 获取插入的矿井ID

                        table_name = self.createTable(file_name, well_name)  # 创建表

                        for index, row in df.iterrows():
                            depth = row.iloc[0]
                            gas_content = row.iloc[1]
                            rock_type = row.iloc[2]
                            brittleness_index = row.iloc[3]
                            critical_strain_rate = row.iloc[4]
                            stress_difference_coefficient = row.iloc[5]
                            fracture_pressure = row.iloc[6]
                            compressive_strength = row.iloc[7]
                            permeability = row.iloc[8]
                            sweetness = row.iloc[9]

                            # 插入到动态生成的矿井数据表
                            insert_well_data = f"""
                                INSERT INTO `{table_name}` (矿井ID, 井深, 含气量, 岩性, 脆性指数,
                                                            临界应变能释放率, 地应力差异系数, 破裂压力,
                                                            抗压强度, 渗透率, 甜度)
                                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                            """
                            cursor.execute(insert_well_data,
                                           (well_id, depth, gas_content, rock_type, brittleness_index,
                                            critical_strain_rate, stress_difference_coefficient,
                                            fracture_pressure, compressive_strength, permeability, sweetness))

                    # 显示成功消息
                    QMessageBox.information(None, "导入成功", "数据已成功导入到数据库。")
                    self.itemDataArea.populateTable()

            except Exception as e:
                if conn:
                    conn.rollback()  # 回滚事务
                error_message = str(e)
                print(error_message)
                QMessageBox.critical(None, "导入失败", f"发生错误: {error_message}")
                return 0

            finally:
                if conn:
                    dbUtil.close_db_conn(conn)

    def createTable(self, item_name, well_name):

        # 连接到数据库
        conn = dbUtil.get_db_conn()
        if not conn:
            QMessageBox.critical(None, "连接失败", "无法连接到数据库。")
            return

        try:
            with conn.cursor() as cursor:
                f_table_name = f"well_{item_name}_{well_name}"
                sql = f"""CREATE TABLE IF NOT EXISTS `{f_table_name}` (
                    矿井数据ID INT AUTO_INCREMENT PRIMARY KEY,
                    矿井ID INT NOT NULL,
                    井深 DECIMAL(15, 5) NOT NULL COMMENT '单位：m',
                    含气量 DECIMAL(25, 15) NOT NULL COMMENT '单位：cm3/g',
                    岩性 DECIMAL(15, 5) NOT NULL,
                    脆性指数 DECIMAL(15, 9) NOT NULL COMMENT '单位：%',
                    临界应变能释放率 DECIMAL(25, 15) NOT NULL,
                    地应力差异系数 DECIMAL(25, 15) NOT NULL,
                    破裂压力 DECIMAL(25, 15) NOT NULL COMMENT '单位：MPa',
                    抗压强度 DECIMAL(25, 15) NOT NULL COMMENT '单位：MPa',
                    渗透率 DECIMAL(25, 15) NOT NULL COMMENT '单位：mD',
                    甜度 DECIMAL(25, 15) NOT NULL,
                    FOREIGN KEY (矿井ID) REFERENCES cq_well(矿井ID)
                );"""
                cursor.execute(sql)
                return f_table_name

        except Exception as e:
            if conn:
                conn.rollback()  # 回滚事务
            error_message = str(e)
            print(error_message)
            QMessageBox.critical(None, "创建失败", f"发生错误: {error_message}")
            return 0

        finally:
            if conn:
                dbUtil.close_db_conn(conn)


class DataStore(QWidget):
    def __init__(self):
        super().__init__()
        self.setMinimumSize(QSize(900, 700))
        self.setupUi()

    def setupUi(self):
        self.layout = QVBoxLayout(self)
        self.layout.setSpacing(14)
        self.layout.setContentsMargins(0, 0, 0, 0)
        self.topBar = TopBar()
        self.topBar.setContentsMargins(0, 0, 0, 0)
        self.itemDataArea = ItemDataArea()
        self.itemBar = ItemSelectBar(self.itemDataArea)  # 传递 itemDataArea 实例
        self.buttonBar = ButtonBar(self.itemDataArea)
        self.scrollAreaWidget = self.itemDataArea


        topBarSeparator = QFrame()
        topBarSeparator.setFrameShape(QFrame.Shape.HLine)
        topBarSeparator.setFrameShadow(QFrame.Shadow.Sunken)

        itemBarSeparator = QFrame()
        itemBarSeparator.setFrameShape(QFrame.Shape.HLine)
        itemBarSeparator.setFrameShadow(QFrame.Shadow.Sunken)

        self.layout.addWidget(self.topBar)
        self.layout.addWidget(topBarSeparator, 0, Qt.AlignmentFlag.AlignCenter)
        self.layout.addWidget(self.itemBar)
        self.layout.addWidget(itemBarSeparator, 0, Qt.AlignmentFlag.AlignCenter)
        self.layout.addWidget(self.scrollAreaWidget)
        self.layout.addWidget(self.buttonBar)


if __name__ == "__main__":
    import sys

    app = QApplication(sys.argv)
    window = DataStore()
    window.show()
    sys.exit(app.exec())
